# coding=utf-8
import pymysql

from Sever.database import conf


def format_list(datas, fields):
    _list = []
    for info in datas:
        _dict = dict()
        for key, _field in enumerate(fields):
            _dict.update({_field: info[key]})
        _list.append(_dict)
    return _list


class DB(object):
    def __init__(self, config=None):
        if config:
            self.config = config
        else:
            self.config = conf.db[0]
        self.db = pymysql.connect(host=self.config['host'],
                                  user=self.config['user'],
                                  password=self.config['password'],
                                  db=self.config['dbname'],
                                  port=self.config['port'])
        self.sql = ''
        self.cursor = self.db.cursor()
        self.sql = ""

    def getTable(self, db_name, db=False):
        if db:
            return self.config['prefix'] + db_name
        return self.config['dbname'] + '.' + self.config['prefix'] + db_name

    def format_where(self, where):
        if isinstance(where, dict):
            req_where = ''
            for field, item in where.items():
                req_where += "{0} = '{1}' AND ".format(field, item)
            return req_where[:-4]
        else:
            return where

    def db_close(self):
        self.cursor.close()
        self.db.close()

    def query(self, sql, fetchSql=False):
        self.sql = sql
        req = self.db.query(self.sql)
        self.db.commit()
        if fetchSql:
            return sql
        self.db_close()
        return req

    def fetch(self, sql, fetchSql=False):
        if fetchSql:
            return sql
        self.sql = sql
        self.cursor.execute(sql)
        req = self.cursor.fetchall()
        _res = []
        for item in req:
            _res.append(item[0])
        self.db_close()
        return _res

    def select(self, table_name, fields='*', where=None, fetchSql=False, db_name=''):
        if db_name == '':
            db_name = self.config['dbname']
        self.sql = "SELECT {0} from {1}".format(fields, self.getTable(table_name))
        if where:
            where = self.format_where(where)
            self.sql = "SELECT {0} from {1} WHERE {2}".format(fields, self.getTable(table_name), where, db_name)
        if fetchSql:
            return self.sql
        self.cursor.execute(self.sql)
        req = self.cursor.fetchall()
        if fields != '*':
            res = format_list(req, fields.split(','))
        else:
            sql_colname = "select COLUMN_NAME from information_schema.COLUMNS where TABLE_NAME = '{0}' and table_schema = '{1}'" \
                .format(self.getTable(table_name, True), self.config['dbname'])
            fields = self.fetch(sql_colname)
            res = format_list(req, fields)
        return res

    def add(self, table_name, data, fetchSql=False):
        keys = []
        items = []
        for key, item in data.items():
            keys.append(key)
            items.append(item)
        str_key = ''
        str_item = ''
        for x in keys:
            str_key += '{0},'.format(x)
        str_key = str_key[:-1]
        for x in items:
            str_item += "'{0}',".format(x)
        str_item = str_item[:-1]
        self.sql = "INSERT INTO {0}({1})VALUES ({2})".format(self.getTable(table_name), str_key, str_item)
        if fetchSql:
            return self.sql
        self.cursor.execute(self.sql)
        self.db.commit()
        self.db_close()
        return self.cursor.lastrowid

    def delete(self, table_name, where, fetchSql=False):
        where = self.format_where(where)
        self.sql = "DELETE FROM {0} WHERE {1}".format(self.getTable(table_name), where)
        if fetchSql:
            return self.sql
        res = self.cursor.execute(self.sql)
        self.db.commit()
        self.db_close()
        if res:
            return True
        return False

    def update(self, table_name, data, where=None, fetchSql=False):
        if isinstance(data, dict):
            if where:
                where = self.format_where(where)
            else:
                if (data.has_key('id')):
                    where = ' id = ' + str(data['id'])
                    del data['id']
                else:
                    raise AttributeError('')
            data = self.format_where(data)
        self.sql = "UPDATE {0} SET {1} WHERE {2}".format(self.getTable(table_name), data, where)
        if fetchSql:
            return self.sql
        res = self.cursor.execute(self.sql)
        self.db.commit()
        self.db_close()
        if res:
            return True
        return False
